package com.xishu.dao.jpa;

import com.xishu.entity.order.Order;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import javax.persistence.Tuple;
import javax.transaction.Transactional;
import java.util.List;

public interface OrderDao extends JpaRepository<Order, Long>, JpaSpecificationExecutor<Order> {

    /**
     * 查询订单，不关心是否已经删除了
     *
     * @param shopId
     * @param orderId
     * @return
     */
    @Query(value = "select  *  from shop_order m where m.shop_id = :shopId " +
            "and m.id = :orderId ", nativeQuery = true)
    public Order findOrder(Long shopId, Long orderId);

    /**
     * 查询营业开始时间到当时时间的所有订单
     * 没有付款的
     * 也没有退款的订单
     * 必须要是父订单
     * 时间没有超时的订单
     * 必须是C端的先付款的订单，后付款的订单不用处理，后付款的订单直接就是打印菜品
     *
     * @param shopId
     * @param startTime
     * @param endTime
     * @param lockItemNumberOrder
     * @param parentOrder
     * @param timeExpiredOrder
     * @return
     */
    @Query(value = "select  *  from shop_order m where m.shop_id = :shopId " +
            "and m.create_time >= :startTime " +
            "and m.create_time <= :endTime " +
            "and m.lock_item_number_order = :lockItemNumberOrder " +
            "and m.parent_order = :parentOrder " +
            "and m.confirm_time < :orderExpireTime " +
            "and m.time_expired_order = :timeExpiredOrder", nativeQuery = true)
    public List<Order> findLockCountOrderList(Long shopId, Long startTime, Long endTime, Boolean lockItemNumberOrder, Boolean parentOrder, Boolean timeExpiredOrder, Long orderExpireTime);

    /**
     * 查询固定餐桌订单
     *
     * @param shopId
     * @param startTime
     * @param endTime
     * @return
     */
    @Query(value = "select  *  from shop_order m where m.shop_id = :shopId " +
            "and m.create_time >= :startTime " +
            "and m.create_time <= :endTime " +
            "and m.unique_code = 0 " +
            "and m.parent_order = true " +
            "and m.flag_delete != true "
            , nativeQuery = true)
    public List<Order> findFixOrder(Long shopId, Long startTime, Long endTime);

    /**
     * 按照时间来查询订单
     * <p>
     * 只查询父订单，并且没有被删除的订单
     *
     * @param shopId
     * @param startTime
     * @param endTime
     * @return
     */
    @Query(value = "select  *  from shop_order m where m.shop_id = :shopId " +
            " and m.create_time >= :startTime " +
            " and m.create_time <= :endTime " +
            " and ( (m.parent_order = :parentOrder and m.payed_once =  :payedOnce ) or expired_order = 1 )" +
            " " +
            " and m.flag_delete = :flagDelete order by create_time ", nativeQuery = true)
    public List<Order> findTimeOrder(Long shopId, Long startTime, Long endTime, Boolean parentOrder, Boolean payedOnce, Boolean flagDelete);


    /**
     * 按照时间来查询订单
     * <p>
     * 只查询父订单，并且没有被删除的订单
     *
     * @param shopId
     * @param startTime
     * @param endTime
     * @return
     */
    @Query(value = "select  *  from shop_order m where m.shop_id = :shopId " +
            " and m.create_time >= :startTime " +
            " and m.create_time <= :endTime " +
            " and ( (m.parent_order = :parentOrder and m.payed_once = true ) or (m.parent_order = :parentOrder and m.send_kitchen = true ) or expired_order = 1 )" +
            " " +
            " and m.flag_delete = :flagDelete order by create_time ", nativeQuery = true)
    public List<Order> findTimeOrderCloseShop(Long shopId, Long startTime, Long endTime, Boolean parentOrder, Boolean flagDelete);

    /**
     * 所有订单均不可见
     *
     * @param shopId
     * @return
     */
    @Modifying
    @Transactional
    @Query("update  shop_order set businessEndShow = false  where shopId=:shopId and createTime >= :startCreateTime and createTime <= :endCreateTime")
    public void cleanOrderBusinessEndShow(long shopId, Long startCreateTime, Long endCreateTime);

    /**
     * 将没有结束的订单关闭
     *
     * @param shopId
     * @param startCreateTime
     * @param endCreateTime
     */
    @Modifying
    @Transactional
    @Query("update shop_order set close = true  where shopId=:shopId and createTime >= :startCreateTime and createTime <= :endCreateTime and status != 100")
    public void closeOrder(Long shopId, Long startCreateTime, Long endCreateTime);

    /**
     * 查询指定时间范围内在B端可见的订单，包含直接在B端添加的订单以及C端落单的订单,只是统计订单数量，不关心订单是否已经取消了
     *
     * @param shopId
     * @param startTime
     * @param endTime
     * @param businessEndShow
     * @return
     */
    @Query("select  count(*) from shop_order m where m.shopId =:shopId and m.confirmTime >= :startTime and m.confirmTime <= :endTime and m.businessEndShow=:businessEndShow and m.parentOrder=:parentOrder")
    public Long countBusinessEndShow(long shopId, long startTime, long endTime, Boolean businessEndShow, Boolean parentOrder);

    /**
     * 查询指定时间范围的销售额，并且按照5分钟的间隔来实现
     * 已支付，未退款
     * 统计的是支付订单
     *
     * @param shopIdList
     * @param startTime
     * @param endTime
     * @param timeUnit
     * @param parentOrder
     * @param payed
     * @param returnCash
     * @return
     */
    @Query("select m.shopId as shopId,sum(userCost) as cash, m.payTime/:timeUnit as time from shop_order m where m.shopId in (:shopIdList) " +
            "and m.payTime >= :startTime " +
            "and m.payTime <= :endTime " +
            "and m.parentOrder=:parentOrder " +
            "and m.payed=:payed " +
            "and m.returnCash=:returnCash " +
            "and m.flagDelete!=true " +
            "group by FLOOR(m.payTime/:timeUnit),m.shopId")
    public List<Tuple> sumSaleUnit(List<Long> shopIdList, Long startTime, Long endTime, Long timeUnit, Boolean parentOrder, Boolean payed, Boolean returnCash);

    /**
     * 查询指定时间范围的订单数量，必须要是已经支付的订单
     *
     * @param shopId
     * @param startTime
     * @param endTime
     * @param parentOrder
     * @return
     */
    @Query("select count(*) as orderNumber from shop_order m where m.shopId =:shopId " +
            "and m.payTime >= :startTime " +
            "and m.payTime <= :endTime " +
            "and m.parentOrder=:parentOrder " +
            "and m.flagDelete!=true " +
            "and m.status in (8,9,10,100)")
    public List<Tuple> sumCount(Long shopId, Long startTime, Long endTime, Boolean parentOrder);

    /**
     * 查询指定时间范围内的订单销售额，未退款
     *
     * @param shopId
     * @param startTime
     * @param endTime
     * @param parentOrder
     * @param payed
     * @param returnCash
     * @return
     */
    @Query("select  sum(userCost) as cash  from shop_order m where m.shopId =:shopId " +
            "and m.payTime >= :startTime " +
            "and m.payTime <= :endTime " +
            "and m.parentOrder=:parentOrder " +
            "and m.payed=:payed " +
            "and m.flagDelete!=true " +
            "and m.returnCash=:returnCash")
    public List<Tuple> sumSale(Long shopId, Long startTime, Long endTime, Boolean parentOrder, Boolean payed, Boolean returnCash);

    /**
     * 当日销售额
     *
     * @param shopIdList
     * @param startTime
     * @param endTime
     * @param parentOrder
     * @param payed
     * @param returnCash
     * @return
     */
    @Query("select  shopId as shopId,sum(userCost) as cash  from shop_order m where m.shopId in (:shopIdList) " +
            "and m.payTime >= :startTime " +
            "and m.payTime <= :endTime " +
            "and m.parentOrder=:parentOrder " +
            "and m.payed=:payed " +
            "and m.flagDelete!=true " +
            "and m.returnCash=:returnCash" +
            " group by m.shopId")
    public List<Tuple> sumSaleList(List<Long> shopIdList, Long startTime, Long endTime, Boolean parentOrder, Boolean payed, Boolean returnCash);


    /**
     * 查询指定时间范围内订单的销售额
     *
     * @param shopIdList
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("select  sum(userCost) as cash  from shop_order m where m.shopId in (:shopIdList) " +
            "and m.payTime >= :startTime " +
            "and m.payTime <= :endTime " +
            "and m.parentOrder=:parentOrder " +
            "and m.payed=:payed " +
            "and m.flagDelete!=true " +
            "and m.returnCash=:returnCash")
    public Double sumSale(List<Long> shopIdList, Long startTime, Long endTime, Boolean parentOrder, Boolean payed, Boolean returnCash);

    /**
     * 这段时间的平均销售额
     *
     * @param shopIdList
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("select  sum(userCost) / :days as cash  from shop_order m where m.shopId in (:shopIdList) " +
            "and m.payTime >= :startTime " +
            "and m.payTime <= :endTime " +
            "and m.parentOrder=true " +
            "and m.payed=true " +
            "and m.flagDelete!=true " +
            "and m.returnCash != true")
    public Double avgSale(List<Long> shopIdList, Long startTime, Long endTime, double days);

    /**
     * 统计指定时间范围内的支付订单
     *
     * @param shopId
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("select  count(*) from shop_order m where m.shopId =:shopId " +
            "and m.payTime >= :startTime " +
            "and m.payTime <= :endTime " +
            "and m.status in (8,9,10,100) " +
            "and m.flagDelete!=true " +
            "and m.parentOrder=:parentOrder")
    public Long countPayedOrder(long shopId, long startTime, long endTime, Boolean parentOrder);

    /**
     * 查询指定范围内的确定的落单
     *
     * @param shopId
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("select  count(*) from shop_order m where m.shopId =:shopId " +
            "and m.confirmTime >= :startTime " +
            "and m.confirmTime <= :endTime " +
            "and m.flagDelete!=true " +
            "and m.parentOrder=:parentOrder")
    public Long countTimeConfirmOrder(Long shopId, Long startTime, Long endTime, Boolean parentOrder);

    /**
     * 查询指定时间范围内用户已支付的订单数量
     *
     * @param shopId
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("select  count(*) from shop_order m where m.shopId =:shopId " +
            "and m.payTime >= :startTime " +
            "and m.payTime <= :endTime " +
            "and m.flagDelete!=true " +
            "and m.parentOrder=:parentOrder")
    public Long countTimePayedOrder(Long shopId, Long startTime, Long endTime, Boolean parentOrder);

    /**
     * 统计指定时间的外卖平台的销售额
     * //外卖平台
     * public static final int ORDER_TYPE_TAKE_OUT_PLAT = 3;
     *
     * @param shopId
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("select  sum(costOrigin) from shop_order m where m.shopId =:shopId " +
            "and m.createTime >= :startTime " +
            "and m.createTime <= :endTime " +
            "and m.flagDelete!=true " +
            "and m.orderType = 3")
    public Double sumTakeOutPlat(Long shopId, Long startTime, Long endTime);

    /**
     * 统计分店收到的现金
     *
     * @param shopId
     * @param startTime
     * @param endTime
     * @param parentOrder
     * @return
     */
    @Deprecated
    @Query("select  sum(userCost) from shop_order m where m.shopId =:shopId and m.createTime >= :startTime and m.createTime <= :endTime and m.parentOrder = :parentOrder and m.payed = :payed")
    public Double sumUserCost(Long shopId, Long startTime, Long endTime, Boolean parentOrder, Boolean payed);

    /**
     * 统计指定时间的外卖平台的订单量
     * //外卖平台
     * * public static final int ORDER_TYPE_TAKE_OUT_PLAT = 3;
     *
     * @param shopId
     * @param startTime
     * @param endTime
     * @return
     */
    @Deprecated
    @Query("select  count(*) from shop_order m where m.shopId =:shopId " +
            "and m.createTime >= :startTime " +
            "and m.createTime <= :endTime " +
            "and m.parentOrder = false " +
            "and m.orderType = 3")
    public Integer countTakeOutPlat(Long shopId, Long startTime, Long endTime);

    /**
     * 统计支付方式的金额
     * 指定时间范围内的，已支付的订单，并且是支付订单，并且是没有退费的
     *
     * @param shopId
     * @param startTime
     * @param endTime
     * @param payType   支付类型
     * @param payed     true
     * @return
     */
    @Query("select  sum(m.userCost) from shop_order m where m.shopId =:shopId " +
            "and m.createTime >= :startTime " +
            "and m.createTime <= :endTime " +
            "and m.payed=:payed " +
            "and  (m.parentOrder=false or m.expiredOrder=true) " +
            "and m.payType=:payType " +
            "and m.subOrder=false " +
            "and m.returnCash=:returnCash " +
            "and m.flagDelete=false "
    )
    public Double sumCash(Long shopId, Long startTime, Long endTime, Integer payType, Boolean payed, Boolean returnCash);

    /**
     * @param shopId
     * @param startTime
     * @param endTime
     * @param payType     支付类型
     * @param parentOrder false
     * @param payed       true
     * @return
     */
    @Query("select  sum(m.userCost) from shop_order m where m.shopId =:shopId " +
            "and m.createTime >= :startTime " +
            "and m.createTime <= :endTime " +
            "and ((m.payed=:payed and  m.parentOrder=:parentOrder) or  m.expiredOrder = true)" +
            "and m.orderType in (1,4) " +
            "and m.payType=:payType " +
            "and m.subOrder=false " +
            "and m.returnCash=:returnCash " +
            "and m.flagDelete=false "
    )
    public Double sumTangShiCash(Long shopId, Long startTime, Long endTime, Integer payType, Boolean parentOrder, Boolean payed, Boolean returnCash);

    /**
     * @param shopId
     * @param startTime
     * @param endTime
     * @param payType     支付类型
     * @param parentOrder false
     * @param payed       true
     * @return
     */
    @Query("select  sum(m.userCost) from shop_order m where m.shopId =:shopId " +
            "and m.createTime >= :startTime " +
            "and m.createTime <= :endTime " +
            "and ((m.payed=:payed and  m.parentOrder=:parentOrder ) or m.expiredOrder = true)" +
            "and m.orderType in(2,3) " +
            "and m.subOrder=false " +
            "and m.payType=:payType " +
            "and m.returnCash=:returnCash " +
            "and m.flagDelete=false "
    )
    public Double sumTakeOutCash(Long shopId, Long startTime, Long endTime, Integer payType, Boolean parentOrder, Boolean payed, Boolean returnCash);

    /**
     * @param shopId
     * @param startTime
     * @param endTime
     * @param payType
     * @return
     */
    @Query("select sum(m.userCost) from shop_order m where m.shopId=:shopId " +
            "and m.createTime >= :startTime " +
            "and m.createTime <= :endTime " +
            "and m.payType=:payType " +
            "and m.vipOrder=true " +
            "and m.payed=true " +
            "and m.flagDelete!=true " +
            "and m.returnCash!=true"
    )
    public Double sumVip(Long shopId, Long startTime, Long endTime, Integer payType);

    /**
     * 只统计总的订单里面的，已支付的订单里面的服务费
     *
     * @param shopId
     * @param startTime
     * @param endTime
     * @param parentOrder
     * @param payed
     * @return
     */
    @Query("select  sum(m.serviceFee) from shop_order m where m.shopId =:shopId " +
            "and m.createTime >= :startTime " +
            "and m.createTime <= :endTime " +
            "and m.payed=:payed " +
            "and  m.parentOrder=:parentOrder " +
            "and m.flagDelete!=true " +
            "and m.expiredOrder != true")
    public Double sumServiceFee(Long shopId, Long startTime, Long endTime, Boolean parentOrder, Boolean payed);

    /**
     * 只统计总的订单里面的，已支付的订单里面的包装费
     *
     * @param shopId
     * @param startTime
     * @param endTime
     * @param parentOrder
     * @param payed
     * @return
     */
    @Query("select  sum(m.packageFee) from shop_order m where m.shopId =:shopId " +
            "and m.createTime >= :startTime " +
            "and m.createTime <= :endTime " +
            "and m.payed=:payed " +
            "and  m.parentOrder=:parentOrder " +
            "and m.flagDelete!=true " +
            "and m.expiredOrder != true")
    public Double sumPackageFee(Long shopId, Long startTime, Long endTime, Boolean parentOrder, Boolean payed);

    /**
     * 统计小费
     *
     * @param shopId
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("select  sum(m.tipsFee) from shop_order m " +
            "where m.shopId =:shopId and m.createTime >= :startTime " +
            "and m.createTime <= :endTime " +
            "and m.payed= true " +
            "and  m.parentOrder=false " +
            "and m.subOrder = false  " +
            "and m.expiredOrder != true " +
            "and m.flagDelete!=true " +
            "and m.returnCash != true")
    public Double sumTipsFee(Long shopId, Long startTime, Long endTime);

    /**
     * 统计消费税
     *
     * @param shopId
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("select  sum(m.consumptionTax) from shop_order m " +
            "where m.shopId =:shopId and m.createTime >= :startTime " +
            "and m.createTime <= :endTime " +
            "and m.payed= true " +
            "and  m.parentOrder=false " +
            "and m.subOrder = false  " +
            "and m.expiredOrder != true " +
            "and m.flagDelete!=true " +
            "and m.returnCash != true")
    public Double sumConsumptionTax(Long shopId, Long startTime, Long endTime);

    /**
     * 只统计总的订单里面的，已支付的订单里面的外卖优惠
     *
     * @param shopId
     * @param startTime
     * @param endTime
     * @param parentOrder
     * @param payed
     * @return
     */
    @Query("select  sum(m.takeOutDiscountFee) from shop_order m where m.shopId =:shopId " +
            "and m.createTime >= :startTime " +
            "and m.createTime <= :endTime " +
            "and m.payed=:payed " +
            "and  m.parentOrder=:parentOrder " +
            "and m.flagDelete!=true " +
            "and m.expiredOrder != true")
    public Double sumTakeOutFee(Long shopId, Long startTime, Long endTime, Boolean parentOrder, Boolean payed);

    /**
     * 只统计支付订单里面，已经支付并且没有退款的优惠券的数据
     *
     * @param shopId
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("select  sum(m.discountFee) from shop_order m where m.shopId =:shopId " +
            "and m.createTime >= :startTime " +
            "and m.createTime <= :endTime " +
            " and m.payed= true " +
            "and  m.parentOrder != true " +
            "and m.expiredOrder != true " +
            "and m.flagDelete!=true " +
            "and m.returnCash != true")
    public Double sumDiscountFee(Long shopId, Long startTime, Long endTime);

    /**
     * 堂食里面未支付的订单数量
     *
     * @param shopId
     * @param startTime
     * @param endTime
     * @param parentOrder
     * @param payed
     * @param sendKitchen
     * @return
     */
    @Query("select sum(m.leftCash)  from shop_order m where m.shopId =:shopId " +
            "and m.createTime >= :startTime " +
            "and m.createTime <= :endTime " +
            "and m.payed=:payed " +
            "and (m.parentOrder=:parentOrder  and m.expiredOrder != true) " +
            "and m.combineOrder != true " +
            "and m.sendKitchen=:sendKitchen " +
            "and m.orderType in (1,4) " +
            "and m.flagDelete=:flagDelete " +
            "and m.returnCash=:returnCash")
    public Double sumSendToKitchenUnpaidForTangShi(Long shopId, Long startTime, Long endTime, Boolean parentOrder, Boolean payed, Boolean sendKitchen, Boolean flagDelete, Boolean returnCash);

    /**
     * 外卖里面未支付的订单数量
     *
     * @param shopId
     * @param startTime
     * @param endTime
     * @param parentOrder
     * @param payed
     * @param sendKitchen
     * @return
     */
    @Query("select  sum(m.leftCash) from shop_order m where m.shopId =:shopId " +
            "and m.createTime >= :startTime " +
            "and m.createTime <= :endTime " +
            "and m.payed=:payed " +
            "and (m.parentOrder=:parentOrder  and m.expiredOrder != true) " +
            "and m.combineOrder != true " +
            "and m.sendKitchen=:sendKitchen " +
            "and m.orderType in (2) " +
            "and m.flagDelete=:flagDelete " +
            "and m.returnCash=:returnCash")
    public Double sumSendToKitchenUnpaidForTakeOut(Long shopId, Long startTime, Long endTime, Boolean parentOrder, Boolean payed, Boolean sendKitchen, Boolean flagDelete, Boolean returnCash);

    /**
     * 只统计总的订单里面的,已经发送到厨房，但是未支付的订单
     *
     * @param shopId
     * @param startTime
     * @param endTime
     * @param parentOrder
     * @param payed
     * @param sendKitchen
     * @return
     */
    @Query("select  sum(m.leftCash) from shop_order m where m.shopId =:shopId " +
            "and m.createTime >= :startTime " +
            "and m.createTime <= :endTime " +
            "and m.payed=:payed " +
            "and (m.parentOrder=:parentOrder  and m.expiredOrder != true) " +
            "and m.combineOrder != true " +
            "and m.sendKitchen=:sendKitchen " +
            "and m.flagDelete=:flagDelete " +
            "and m.returnCash=:returnCash")
    public Double sumSendToKitchenUnpaid(Long shopId, Long startTime, Long endTime, Boolean parentOrder, Boolean payed, Boolean sendKitchen, Boolean flagDelete, Boolean returnCash);

    /**
     * 已退款金额
     *
     * @param shopId
     * @param startTime
     * @param endTime
     * @param parentOrder
     * @param payed
     * @param returnCash
     * @return
     */
    @Query("select  sum(m.userCost) from shop_order m where m.shopId =:shopId " +
            "and m.createTime >= :startTime " +
            "and m.createTime <= :endTime " +
            "and m.payed=:payed and  " +
            "m.parentOrder=:parentOrder " +
            "and m.subOrder=false " +
            "and m.returnCash=:returnCash")
    public Double returnCash(Long shopId, Long startTime, Long endTime, Boolean parentOrder, Boolean payed, Boolean returnCash);

    /**
     * 堂食里面的退款金额
     *
     * @param shopId
     * @param startTime
     * @param endTime
     * @param parentOrder
     * @param payed
     * @param returnCash
     * @return
     */
    @Query("select  sum(m.userCost) from shop_order m where m.shopId =:shopId " +
            "and m.createTime >= :startTime " +
            "and m.createTime <= :endTime " +
            "and m.payed=:payed " +
            "and m.parentOrder=:parentOrder " +
            "and m.returnCash=:returnCash " +
            "and m.subOrder=false " +
            "and m.orderType in (1,4) ")
    public Double returnCashForTangShi(Long shopId, Long startTime, Long endTime, Boolean parentOrder, Boolean payed, Boolean returnCash);

    /**
     * 外卖里面的退款金额
     *
     * @param shopId
     * @param startTime
     * @param endTime
     * @param parentOrder
     * @param payed
     * @param returnCash
     * @return
     */
    @Query("select  sum(m.userCost) from shop_order m where m.shopId =:shopId " +
            "and m.createTime >= :startTime " +
            "and m.createTime <= :endTime " +
            "and m.payed=:payed " +
            "and m.parentOrder=:parentOrder " +
            "and m.subOrder=false " +
            "and m.returnCash=:returnCash " +
            "and m.orderType in (2) ")
    public Double returnCashForTakeOut(Long shopId, Long startTime, Long endTime, Boolean parentOrder, Boolean payed, Boolean returnCash);

    /**
     * 已经支付的订单里面的人数
     *
     * @param shopId
     * @param startTime
     * @param endTime
     * @param parentOrder
     * @param payed
     * @return
     */
    @Query("select  sum(m.persons) from shop_order m where m.shopId =:shopId " +
            "and m.createTime >= :startTime " +
            "and m.createTime <= :endTime " +
            "and m.payed=:payed " +
            "and m.flagDelete!=true " +
            "and  m.parentOrder=:parentOrder  ")
    public Integer dinners(Long shopId, Long startTime, Long endTime, Boolean parentOrder, Boolean payed);

    /**
     * 订单订单，并且没有退款的
     *
     * @param shopId
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("select  count(*) from shop_order m where m.shopId =:shopId and m.createTime >= :startTime and m.createTime <= :endTime " +
            " and m.payed=true and  m.parentOrder != true  " +
            " and m.subOrder != true " +
            " and m.returnCash != true " +
            " and m.flagDelete=false "
    )
    public Integer orderNumber(Long shopId, Long startTime, Long endTime);

    /**
     * 计算堂食订单的数量
     *
     * @param shopId
     * @param startTime
     * @param endTime
     * @param parentOrder
     * @param payed
     * @return
     */
    @Query("select  count(*) from shop_order m where m.shopId =:shopId " +
            "and m.createTime >= :startTime " +
            "and m.createTime <= :endTime " +
            "and m.payed=:payed " +
            "and  m.parentOrder=:parentOrder " +
            "and m.flagDelete!=true " +
            "and m.orderType in (1,4)")
    public Integer orderTangShiNumber(Long shopId, Long startTime, Long endTime, Boolean parentOrder, Boolean payed);

    /**
     * 计算外卖订单的数量
     *
     * @param shopId
     * @param startTime
     * @param endTime
     * @param parentOrder
     * @param payed
     * @return
     */
    @Query("select  count(*) from shop_order m where m.shopId =:shopId " +
            "and m.createTime >= :startTime " +
            "and m.createTime <= :endTime " +
            "and m.payed=:payed " +
            "and  m.parentOrder=:parentOrder " +
            "and m.orderType in (2)")
    public Integer orderTakeOutNumber(Long shopId, Long startTime, Long endTime, Boolean parentOrder, Boolean payed);

    /**
     * 删除的订单数量
     *
     * @param shopId
     * @param startTime
     * @param endTime
     * @param parentOrder
     * @param flagDelete  标记删除的订单
     * @return
     */
    @Query("select  count(*) from shop_order m where m.shopId =:shopId " +
            "and m.createTime >= :startTime " +
            "and m.createTime <= :endTime " +
            "and m.parentOrder=:parentOrder " +
            "and m.flagDelete=:flagDelete " +
            "and m.sendKitchen=:sendKitchen")
    public Integer deleteOrderNumber(Long shopId, Long startTime, Long endTime, Boolean parentOrder, Boolean flagDelete, Boolean sendKitchen);

    /**
     * 查询所有已经支付的订单
     *
     * @param shopId
     * @param startTime
     * @param endTime
     * @param parentOrder
     * @param payed
     * @return
     */
    @Query(value = "select * from shop_order m where m.shop_id = :shopId " +
            "and m.create_time >= :startTime " +
            "and m.create_time <= :endTime " +
            "and m.parent_order = :parentOrder " +
            "and m.payed = :payed " +
            "and m.sub_order = false " +
            "and m.flag_delete!=true " +
            "and m.return_cash = :returnCash", nativeQuery = true)
    public List<Order> payedSubOrderList(Long shopId, Long startTime, Long endTime, Boolean parentOrder, Boolean payed, Boolean returnCash);


    /**
     * 订单统计，按照商铺维度
     * 必须要是已归档的数据
     *
     * @param startTime
     * @param endTime
     * @param timeUnit
     * @return
     */
    @Query(value = "select  FROM_UNIXTIME(create_time / 1000,:timeUnit)  as x , shop_id as tmpId, sum(user_cost - left_cash  + ifnull(real_discount_fee, 0)) as value " +
            "from shop_order where  create_time >= :startTime and create_time <= :endTime  and time_fix > 0 and parent_order is true and flag_delete=false and shop_id in (:shopIdList)" +
            "group by shop_id, FROM_UNIXTIME(create_time / 1000, :timeUnit)", nativeQuery = true)
    public List<Tuple> shopReport(@Param("startTime") Long startTime, @Param("endTime") Long endTime, @Param("timeUnit") String timeUnit, @Param("shopIdList") List<Long> shopIdList);

    /**
     * 订单统计，按照商铺维度
     * 必须要是已归档的数据
     *
     * @param startTime
     * @param endTime
     * @param timeUnit
     * @return
     */
    @Query(value = "select FROM_UNIXTIME(create_time / 1000,:timeUnit)  as x , shop_id as tmpId, sum(user_cost - left_cash  + ifnull(real_discount_fee, 0)) as value " +
            "from shop_order where  create_time >= :startTime and create_time <= :endTime  and time_fix > 0 and parent_order is true and flag_delete=false and shop_id=:shopId" +
            " group by shop_id,FROM_UNIXTIME(create_time / 1000, :timeUnit) ",
            nativeQuery = true)
    public List<Tuple> shopReport(@Param("startTime") Long startTime, @Param("endTime") Long endTime, @Param("timeUnit") String timeUnit, @Param("shopId") Long shopId);

    /**
     * 订单统计，按照商铺维度
     * 必须要是已归档的数据
     * 总报告，总计
     *
     * @param startTime
     * @param endTime
     * @param shopIdList
     * @return
     */
    @Query(value = "select  shop_id as tmpId, sum(user_cost - left_cash  + ifnull(real_discount_fee, 0)) as value " +
            "from shop_order where  create_time >= :startTime and create_time <= :endTime  and time_fix > 0 and parent_order is true and flag_delete=false and shop_id in (:shopIdList)" +
            " group by shop_id ",
            nativeQuery = true)
    public List<Tuple> shopReportTotal(@Param("startTime") Long startTime, @Param("endTime") Long endTime, @Param("shopIdList") List<Long> shopIdList);


    /**
     * 统计消费年龄
     * <p>
     * <p>
     * 按照shopId以及年龄来分组
     *
     * @param startTime
     * @param endTime
     * @param timeUnit
     * @param
     * @return
     */
    @Query(value = "select FROM_UNIXTIME(create_time / 1000,:timeUnit)  as x , age as tmpId, sum(user_cost) as value " +
            "from shop_order where  create_time >= :startTime and create_time <= :endTime " +
            " and parent_order is false and flag_delete=false and shop_id in (:shopIdList) and age is not null " +
            " and payed = true and return_cash = false and vip_order is null " +
            " group by age,FROM_UNIXTIME(create_time / 1000, :timeUnit) ",
            nativeQuery = true)
    public List<Tuple> ageReport(@Param("startTime") Long startTime, @Param("endTime") Long endTime, @Param("timeUnit") String timeUnit, @Param("shopIdList") List<Long> shopIdList);

    /**
     * 统计消费年龄
     * <p>
     * <p>
     * 按照shopId以及年龄来分组
     *
     * @param startTime
     * @param endTime
     * @param
     * @return
     */
    @Query(value = "select age as tmpId, sum(user_cost) as value " +
            "from shop_order where  create_time >= :startTime and create_time <= :endTime " +
            " and parent_order is false and flag_delete=false and shop_id in (:shopIdList) and age is not null " +
            " and payed = true and return_cash = false and vip_order is null " +
            " group by age ",
            nativeQuery = true)
    public List<Tuple> ageReportTotal(@Param("startTime") Long startTime, @Param("endTime") Long endTime, @Param("shopIdList") List<Long> shopIdList);

    /**
     * 统计性别
     * <p>
     * <p>
     * 按照shopId以及年龄来分组
     *
     * @param startTime
     * @param endTime
     * @param timeUnit
     * @param
     * @return
     */
    @Query(value = "select FROM_UNIXTIME(create_time / 1000,:timeUnit)  as x , sex as tmpId, sum(user_cost) as value " +
            "from shop_order where  create_time >= :startTime and create_time <= :endTime " +
            " and parent_order is false and flag_delete=false and shop_id in (:shopIdList) and sex is not null " +
            " and payed = true and return_cash = false and vip_order is null " +
            " group by sex,FROM_UNIXTIME(create_time / 1000, :timeUnit) ",
            nativeQuery = true)
    public List<Tuple> sexReport(@Param("startTime") Long startTime, @Param("endTime") Long endTime, @Param("timeUnit") String timeUnit, @Param("shopIdList") List<Long> shopIdList);

    /**
     * 统计性别
     * <p>
     * <p>
     *
     * @param startTime
     * @param endTime
     * @param
     * @return
     */
    @Query(value = "select sex as tmpId, sum(user_cost) as value " +
            "from shop_order where create_time >= :startTime and create_time <= :endTime " +
            " and parent_order is false and flag_delete=false and shop_id in (:shopIdList) and sex is not null " +
            " and payed = true and return_cash = false and vip_order is null " +
            " group by sex",
            nativeQuery = true)
    public List<Tuple> sexReportTotal(@Param("startTime") Long startTime, @Param("endTime") Long endTime, @Param("shopIdList") List<Long> shopIdList);

    /**
     * 客单价
     * <p>
     * <p>
     * 按照shopId以及年龄来分组
     *
     * @param startTime
     * @param endTime
     * @param timeUnit
     * @param
     * @return
     */
    @Query(value = "select FROM_UNIXTIME(create_time / 1000,:timeUnit)  as x ,  sum(user_cost) as value " +
            "from shop_order where  create_time >= :startTime and create_time <= :endTime " +
            " and parent_order is false and flag_delete=false and shop_id in (:shopIdList) and user_cost > 0 and customer_id > 0 " +
            " and payed = true and return_cash = false and vip_order is null " +
            " group by FROM_UNIXTIME(create_time / 1000, :timeUnit) ",
            nativeQuery = true)
    public List<Tuple> customerPerCostReport(@Param("startTime") Long startTime, @Param("endTime") Long endTime, @Param("timeUnit") String timeUnit, @Param("shopIdList") List<Long> shopIdList);

    /**
     * 客单价
     * <p>
     * <p>
     * 按照shopId以及年龄来分组
     *
     * @param startTime
     * @param endTime
     * @param
     * @return
     */
    @Query(value = "select sum(user_cost) as value " +
            "from shop_order where  create_time >= :startTime and create_time <= :endTime " +
            " and parent_order is false and flag_delete=false and shop_id in (:shopIdList) and user_cost > 0 and customer_id > 0 " +
            " and payed = true and return_cash = false and vip_order is null ",
            nativeQuery = true)
    public List<Tuple> customerPerCostReportTotal(@Param("startTime") Long startTime, @Param("endTime") Long endTime, @Param("shopIdList") List<Long> shopIdList);

    /**
     * 新客，熟客报表
     * <p>
     * <p>
     * 按照shopId以及年龄来分组
     *
     * @param startTime
     * @param endTime
     * @param timeUnit
     * @param
     * @return
     */
    @Query(value = "select FROM_UNIXTIME(create_time / 1000,:timeUnit)  as x , new_customer_order as tmpId,   sum(user_cost) as value " +
            "from shop_order where  create_time >= :startTime and create_time <= :endTime " +
            " and parent_order is false and flag_delete=false and shop_id in (:shopIdList)  and customer_id > 0 " +
            " and payed = true and return_cash = false and vip_order is null and new_customer_order is not null" +
            " group by new_customer_order,FROM_UNIXTIME(create_time / 1000, :timeUnit) ",
            nativeQuery = true)
    public List<Tuple> newCustomerReport(@Param("startTime") Long startTime, @Param("endTime") Long endTime, @Param("timeUnit") String timeUnit, @Param("shopIdList") List<Long> shopIdList);

    /**
     * 新客，熟客报表
     * <p>
     * <p>
     * 按照shopId以及年龄来分组
     *
     * @param startTime
     * @param endTime
     * @param
     * @return
     */
    @Query(value = "select  new_customer_order as tmpId, sum(user_cost) as value " +
            "from shop_order where  create_time >= :startTime and create_time <= :endTime " +
            " and parent_order is false and flag_delete=false and shop_id in (:shopIdList)  and customer_id > 0 " +
            " and payed = true and return_cash = false and vip_order is null and new_customer_order is not null" +
            " group by new_customer_order ",
            nativeQuery = true)
    public List<Tuple> newCustomerReportTotal(@Param("startTime") Long startTime, @Param("endTime") Long endTime, @Param("shopIdList") List<Long> shopIdList);

    /**
     * 会员等级
     * <p>
     * <p>
     * 按照shopId以及年龄来分组
     *
     * @param startTime
     * @param endTime
     * @param timeUnit
     * @param
     * @return
     */
    @Query(value = "select FROM_UNIXTIME(create_time / 1000,:timeUnit)  as x , customer_level as tmpId,   sum(user_cost) as value " +
            "from shop_order where  create_time >= :startTime and create_time <= :endTime " +
            " and parent_order is false and flag_delete=false and shop_id in (:shopIdList)  and customer_level is not null and customer_id > 0 " +
            " and payed = true and return_cash = false and vip_order is null " +
            " group by customer_level,FROM_UNIXTIME(create_time / 1000, :timeUnit) ",
            nativeQuery = true)
    public List<Tuple> customerLevelReport(@Param("startTime") Long startTime, @Param("endTime") Long endTime, @Param("timeUnit") String timeUnit, @Param("shopIdList") List<Long> shopIdList);

    /**
     * 会员等级
     * <p>
     * <p>
     * 按照shopId以及年龄来分组
     *
     * @param startTime
     * @param endTime
     * @param
     * @return
     */
    @Query(value = "select customer_level as tmpId,   sum(user_cost) as value " +
            "from shop_order where  create_time >= :startTime and create_time <= :endTime " +
            " and parent_order is false and flag_delete=false and shop_id in (:shopIdList)  and customer_level is not null and customer_id > 0 " +
            " and payed = true and return_cash = false and vip_order is null " +
            "group by customer_level",
            nativeQuery = true)
    public List<Tuple> customerLevelReportTotal(@Param("startTime") Long startTime, @Param("endTime") Long endTime, @Param("shopIdList") List<Long> shopIdList);

    /**
     * VIP会员消费的订单数量
     *
     * @param startTime
     * @param endTime
     * @param timeUnit
     * @param shopIdList
     * @return
     */
    @Query(value = "select FROM_UNIXTIME(create_time / 1000,:timeUnit)  as x ,   count(*) as value " +
            "from shop_order where  create_time >= :startTime and create_time <= :endTime " +
            " and parent_order is false and flag_delete=false and shop_id in (:shopIdList)  and customer_vip = true and customer_id > 0 " +
            " and payed = true and return_cash = false " +
            " group by FROM_UNIXTIME(create_time / 1000, :timeUnit) ",
            nativeQuery = true)
    public List<Tuple> vipCustomerOrderCount(@Param("startTime") Long startTime, @Param("endTime") Long endTime, @Param("timeUnit") String timeUnit, @Param("shopIdList") List<Long> shopIdList);

    /**
     * VIP会员消费的订单数量
     *
     * @param startTime
     * @param endTime
     * @param shopIdList
     * @return
     */
    @Query(value = "select count(*) as value " +
            "from shop_order where  create_time >= :startTime and create_time <= :endTime " +
            " and parent_order is false and flag_delete=false and shop_id in (:shopIdList)  and customer_vip = true and customer_id > 0 " +
            " and payed = true and return_cash = false ",

            nativeQuery = true)
    public List<Tuple> vipCustomerOrderCountTotal(@Param("startTime") Long startTime, @Param("endTime") Long endTime, @Param("shopIdList") List<Long> shopIdList);

    /**
     * VIP会员消费的订单数量
     *
     * @param startTime
     * @param endTime
     * @param timeUnit
     * @param shopIdList
     * @return
     */
    @Query(value = "select FROM_UNIXTIME(create_time / 1000,:timeUnit)  as x ,   sum(user_cost) as value " +
            " from shop_order where  create_time >= :startTime and create_time <= :endTime " +
            " and parent_order is false and flag_delete=false and shop_id in (:shopIdList)  and customer_vip = true and customer_id > 0 " +
            " and payed = true and return_cash = false " +
            " group by FROM_UNIXTIME(create_time / 1000, :timeUnit) ",
            nativeQuery = true)
    public List<Tuple> vipCustomerOrderList(@Param("startTime") Long startTime, @Param("endTime") Long endTime, @Param("timeUnit") String timeUnit, @Param("shopIdList") List<Long> shopIdList);

    /**
     * VIP会员消费的订单数量
     *
     * @param startTime
     * @param endTime
     * @param shopIdList
     * @return
     */
    @Query(value = "select  sum(user_cost) as value " +
            " from shop_order where  create_time >= :startTime and create_time <= :endTime " +
            " and parent_order is false and flag_delete=false and shop_id in (:shopIdList)  and customer_vip = true and customer_id > 0 " +
            " and payed = true and return_cash = false ",
            nativeQuery = true)
    public List<Tuple> vipCustomerOrderListTotal(@Param("startTime") Long startTime, @Param("endTime") Long endTime, @Param("shopIdList") List<Long> shopIdList);

    /**
     * VIP订单消费统计
     *
     * @param startTime
     * @param endTime
     * @param timeUnit
     * @param shopIdList
     * @return
     */
    @Query(value = "select FROM_UNIXTIME(create_time / 1000,:timeUnit)  as x , count(*) as value " +
            " from shop_order where  create_time >= :startTime and create_time <= :endTime " +
            " and  flag_delete=false and shop_id in (:shopIdList) " +
            " and  vip_order is true " +
            " and payed = true and return_cash = false " +
            " group by FROM_UNIXTIME(create_time / 1000, :timeUnit) ",
            nativeQuery = true)
    public List<Tuple> customerVipOrderCount(@Param("startTime") Long startTime, @Param("endTime") Long endTime, @Param("timeUnit") String timeUnit, @Param("shopIdList") List<Long> shopIdList);

    /**
     * VIP订单消费统计
     *
     * @param startTime
     * @param endTime
     * @param shopIdList
     * @return
     */
    @Query(value = "select count(*) as value " +
            " from shop_order where  create_time >= :startTime and create_time <= :endTime " +
            " and  flag_delete=false and shop_id in (:shopIdList) " +
            " and  vip_order is true " +
            " and payed = true and return_cash = false ",
            nativeQuery = true)
    public List<Tuple> customerVipOrderCountTotal(@Param("startTime") Long startTime, @Param("endTime") Long endTime, @Param("shopIdList") List<Long> shopIdList);

    /**
     * VIP订单消费统计
     *
     * @param startTime
     * @param endTime
     * @param timeUnit
     * @param shopIdList
     * @return
     */
    @Query(value = "select FROM_UNIXTIME(create_time / 1000,:timeUnit)  as x , sum(user_cost) as value " +
            " from shop_order where  create_time >= :startTime and create_time <= :endTime " +
            " and  flag_delete=false and shop_id in (:shopIdList)  " +
            " and  vip_order is true " +
            " and payed = true and return_cash = false " +
            " group by FROM_UNIXTIME(create_time / 1000, :timeUnit) ",
            nativeQuery = true)
    public List<Tuple> customerVipOrderSum(@Param("startTime") Long startTime, @Param("endTime") Long endTime, @Param("timeUnit") String timeUnit, @Param("shopIdList") List<Long> shopIdList);

    /**
     * VIP订单消费统计
     *
     * @param startTime
     * @param endTime
     * @param shopIdList
     * @return
     */
    @Query(value = "select  sum(user_cost) as value " +
            " from shop_order where  create_time >= :startTime and create_time <= :endTime " +
            " and  flag_delete=false and shop_id in (:shopIdList)  " +
            " and  vip_order is true " +
            " and payed = true and return_cash = false ",
            nativeQuery = true)
    public List<Tuple> customerVipOrderSumTotal(@Param("startTime") Long startTime, @Param("endTime") Long endTime, @Param("shopIdList") List<Long> shopIdList);

    /**
     * 邮件续费VIP统计，这样的订单没有shopId,只有companyId
     *
     * @param startTime
     * @param endTime
     * @param timeUnit
     * @param companyId
     * @return
     */
    @Query(value = "select FROM_UNIXTIME(create_time / 1000,:timeUnit)  as x , sum(user_cost) as value " +
            " from shop_order where  create_time >= :startTime and create_time <= :endTime " +
            " and  flag_delete=false and company_id = :companyId  " +
            " and shop_id is null and  vip_order is true " +
            " and payed = true and return_cash = false " +
            " group by FROM_UNIXTIME(create_time / 1000, :timeUnit) ",
            nativeQuery = true)
    public List<Tuple> customerVipOrderRenewSum(@Param("startTime") Long startTime, @Param("endTime") Long endTime, @Param("timeUnit") String timeUnit, @Param("companyId") Long companyId);

    /**
     * 邮件续费VIP统计，这样的订单没有shopId,只有companyId
     *
     * @param startTime
     * @param endTime
     * @param companyId
     * @return
     */
    @Query(value = "select  sum(user_cost) as value " +
            " from shop_order where  create_time >= :startTime and create_time <= :endTime " +
            " and  flag_delete=false and company_id = :companyId  " +
            " and shop_id is null and  vip_order is true " +
            " and payed = true and return_cash = false ",
            nativeQuery = true)
    public List<Tuple> customerVipOrderRenewSumTotal(@Param("startTime") Long startTime, @Param("endTime") Long endTime, @Param("companyId") Long companyId);

    /**
     * 邮件续费VIP统计，这样的订单没有shopId,只有companyId
     *
     * @param startTime
     * @param endTime
     * @param timeUnit
     * @param companyId
     * @return
     */
    @Query(value = "select FROM_UNIXTIME(create_time / 1000,:timeUnit)  as x , count(*) as value " +
            " from shop_order where  create_time >= :startTime and create_time <= :endTime " +
            " and  flag_delete=false and company_id = :companyId  " +
            " and shop_id is null and  vip_order is true " +
            " and payed = true and return_cash = false " +
            " group by FROM_UNIXTIME(create_time / 1000, :timeUnit) ",
            nativeQuery = true)
    public List<Tuple> customerVipOrderRenewCount(@Param("startTime") Long startTime, @Param("endTime") Long endTime, @Param("timeUnit") String timeUnit, @Param("companyId") Long companyId);

    /**
     * 邮件续费VIP统计，这样的订单没有shopId,只有companyId
     *
     * @param startTime
     * @param endTime
     * @param companyId
     * @return
     */
    @Query(value = "select count(*) as value " +
            " from shop_order where  create_time >= :startTime and create_time <= :endTime " +
            " and  flag_delete=false and company_id = :companyId  " +
            " and shop_id is null and  vip_order is true " +
            " and payed = true and return_cash = false ",
            nativeQuery = true)
    public List<Tuple> customerVipOrderRenewCountTotal(@Param("startTime") Long startTime, @Param("endTime") Long endTime, @Param("companyId") Long companyId);

    /**
     * 已付款的单，没有退单的支付订单
     * 计算净营收
     *
     * @param shopId
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("select  sum(m.userCost) - sum(m.consumptionTax) - sum(m.tipsFee) - sum(m.serviceFee) from shop_order m where m.shopId =:shopId " +
            "and m.createTime >= :startTime " +
            "and m.createTime <= :endTime " +
            "and (m.payed=true and  m.parentOrder=false) " +
            "and m.subOrder=false " +
            "and m.flagDelete!=true " +
            "and m.returnCash!=true "
    )
    public Double sumNetSale(Long shopId, Long startTime, Long endTime);


    /**
     * 已付款的单，没有退单的支付订单
     * 计算净营收 + 消费税
     *
     * @param shopId
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("select  sum(m.userCost) - sum(m.tipsFee) - sum(m.serviceFee) from shop_order m where m.shopId =:shopId " +
            "and m.createTime >= :startTime " +
            "and m.createTime <= :endTime " +
            "and (m.payed=true and  m.parentOrder=false) " +
            "and m.subOrder=false " +
            "and m.flagDelete!=true " +
            "and m.returnCash!=true "
    )
    public Double sumNetSaleWithTax(Long shopId, Long startTime, Long endTime);

    /**
     * 计算小费
     * 小费是按照支付订单来统计，如果支付订单里面没有小费，那么最后就是没有小费
     * <p>
     * 已付款的单，没有退单的支付订单
     *
     * @param shopId
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("select  sum(m.tipsFee)  from shop_order m where m.shopId =:shopId " +
            "and m.createTime >= :startTime " +
            "and m.createTime <= :endTime " +
            "and (m.payed=true and  m.parentOrder=false) " +
            "and m.subOrder=false " +
            "and m.flagDelete!=true " +
            "and m.returnCash!=true "
    )
    public Double sumTips(Long shopId, Long startTime, Long endTime);

    /**
     * 计算所有已经支付完成的订单数量
     * 按照父订单的来统计
     *
     * @param shopIdList
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("select  count(*)  from shop_order m where m.shopId in (:shopIdList) " +
            "and m.createTime >= :startTime " +
            "and m.createTime <= :endTime " +
            "and (m.payed=true and  m.parentOrder=true) " +
            "and m.status=100 " +
            "and m.flagDelete!=true " +
            "and m.returnCash!=true "
    )
    public Integer countCompleteParentOrder(List<Long> shopIdList, Long startTime, Long endTime);

    /**
     * 支付方式总和
     *
     * @param shopIdList
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("select  sum(userCost)  from shop_order m where m.shopId in (:shopIdList) " +
            "and m.createTime >= :startTime " +
            "and m.createTime <= :endTime " +
            "and (m.payed=true and  m.parentOrder=false) " +
            "and m.subOrder != true " +
            "and m.status=100 " +
            "and m.flagDelete!=true " +
            "and m.returnCash!=true "
    )
    public Double totalPayType(List<Long> shopIdList, Long startTime, Long endTime);

    /**
     * 支付方式前几
     *
     * @param shopIdList
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("select  sum(userCost) as value,  payTypeName_zh as y_zh, payTypeName_en as y_en from shop_order m where m.shopId in (:shopIdList) " +
            "and m.createTime >= :startTime " +
            "and m.createTime <= :endTime " +
            "and (m.payed=true and  m.parentOrder=false) " +
            "and m.subOrder != true " +
            "and m.payType is not null " +
            "and m.status=100 " +
            "and m.flagDelete!=true " +
            "and m.returnCash!=true " +
            "group by m.payTypeName_en " +
            "order by sum(userCost) desc "
    )
    public List<Tuple> topNPayType(List<Long> shopIdList, Long startTime, Long endTime);

    /**
     * 客流量统计
     *
     * @param shopIdList
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("select  sum(persons) as value  from shop_order m where m.shopId in (:shopIdList) " +
            "and m.createTime >= :startTime " +
            "and m.createTime <= :endTime " +
            "and (m.payed=true and  m.parentOrder=true) " +
            "and m.status=100 " +
            "and m.flagDelete!=true " +
            "and m.returnCash!=true "
    )
    public Integer sumPerson(List<Long> shopIdList, Long startTime, Long endTime);

    /**
     * 平均客单价
     *
     * @param shopIdList
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("select  sum(userCost) / sum(persons)  from shop_order m where m.shopId in (:shopIdList) " +
            "and m.createTime >= :startTime " +
            "and m.createTime <= :endTime " +
            "and (m.payed=true and  m.parentOrder=true) " +
            "and m.status=100 " +
            "and m.flagDelete!=true " +
            "and m.returnCash!=true " +
            "and m.persons > 0 "
    )
    public Double avgPersonCost(List<Long> shopIdList, Long startTime, Long endTime);

    /**
     * 带支付类型 营业流水表头总计
     *
     * @param shopId
     * @return
     */
    @Query(value = " select " +
            " IFNULL(sum(cost_origin) , 0) as costOrigin, " +
            " IFNULL(sum(discount_fee) , 0) as discountFee, " +
            " IFNULL(sum(persons) , 0) as persons ," +
            " IFNULL(sum(take_out_discount_fee) , 0) as takeOutDiscountFee , " +
            " IFNULL(sum(consumption_tax) , 0) as consumptionTax , " +
            " IFNULL(sum(user_cost) , 0) as userCost," +
            " IFNULL(sum(tips_fee) , 0) as tipsFee," +
            " IFNULL(sum(service_fee) , 0) as serviceFee, " +
            " IFNULL(sum(left_cash) , 0) as leftCash, " +
            " IFNULL(sum(package_fee) , 0) as packageFee " +
            " from shop_order where " +
            " shop_id = :shopId " +
            " and create_time >= :startTime " +
            " and create_time <= :endTime " +
            " and flag_delete = false " +
            " and time_fix is not null " +
            " and return_cash = false " +
            " and expired_order = false " +
            " and pay_type_bit & :payTypeBitNumber !=0 "
            , nativeQuery = true
    )
    List<Tuple> orderHeaderTotalPayType(Long startTime, Long endTime, Long shopId, long payTypeBitNumber);


    /**
     * 营业流水表头总计
     *
     * @param shopId
     * @return
     */
    @Query(value = " select " +
            " IFNULL(sum(cost_origin) , 0) as costOrigin, " +
            " IFNULL(sum(discount_fee) , 0) as discountFee, " +
            " IFNULL(sum(persons) , 0) as persons ," +
            " IFNULL(sum(take_out_discount_fee) , 0) as takeOutDiscountFee , " +
            " IFNULL(sum(consumption_tax) , 0) as consumptionTax , " +
            " IFNULL(sum(user_cost) , 0) as userCost," +
            " IFNULL(sum(tips_fee) , 0) as tipsFee," +
            " IFNULL(sum(service_fee) , 0) as serviceFee, " +
            " IFNULL(sum(left_cash) , 0) as leftCash, " +
            " IFNULL(sum(package_fee) , 0) as packageFee " +
            " from shop_order where " +
            " shop_id = :shopId " +
            " and create_time >= :startTime " +
            " and create_time <= :endTime " +
            " and flag_delete = false " +
            " and return_cash = false " +
            " and expired_order = false " +
            " and time_fix is not null "
            , nativeQuery = true
    )
    List<Tuple> orderHeaderTotal(Long startTime, Long endTime, Long shopId);

    /**
     * 营业流水表头总计
     * status不是100
     *
     * @param shopId
     * @param statusList
     * @return
     */
    @Query(value = " select *" +
            " from shop_order where " +
            " shop_id = :shopId " +
            " and create_time >= :startTime " +
            " and create_time <= :endTime " +
            " and flag_delete = false " +
            " and time_fix is not null " +
            " and status in ( :statusList )"
            , nativeQuery = true
    )
    List<Order> orderHeaderTotalNon100(Long startTime, Long endTime, Long shopId, List<Integer> statusList);

    /**
     * 营业流水表头总计
     * status 是100或者99之类的
     *
     * @param shopId
     * @return
     */
    @Query(value = " select IFNULL(sum(user_cost) , 0) as userCost " +
            " from shop_order where " +
            " shop_id = :shopId " +
            " and create_time >= :startTime " +
            " and create_time <= :endTime " +
            " and flag_delete = false " +
            " and time_fix is not null " +
            " and status =100 "
            , nativeQuery = true
    )
    Double orderHeaderTotal100(Long startTime, Long endTime, Long shopId);


    /**
     * status不是100 带支付类型 营业流水表头总计
     *
     * @param shopId
     * @param statusList
     * @return
     */
    @Query(value = " select * " +
            " from shop_order where " +
            " shop_id = :shopId " +
            " and create_time >= :startTime " +
            " and create_time <= :endTime " +
            " and flag_delete = false " +
            " and time_fix is not null " +
            " and pay_type_bit & :payTypeBitNumber !=0 " +
            " and status in ( :statusList )"
            , nativeQuery = true
    )
    List<Order> idOrderHeaderTotalNon100(Long startTime, Long endTime, Long shopId, long payTypeBitNumber, List<Integer> statusList);

    /**
     * 带id，找到没有退款的失效订单
     *
     * @param startTime
     * @param endTime
     * @param shopId
     * @param payTypeBitNumber
     * @return
     */
    @Query(value = " select * " +
            " from shop_order where " +
            " shop_id = :shopId " +
            " and create_time >= :startTime " +
            " and create_time <= :endTime " +
            " and flag_delete = false " +
            " and time_fix is not null " +
            " and pay_type_bit & :payTypeBitNumber !=0 " +
            " and expired_order =true " +
            "and return_cash = false "
            , nativeQuery = true
    )
    List<Order> idExpiredOrderList(Long startTime, Long endTime, Long shopId, long payTypeBitNumber);

    /**
     * 没有退款的失效订单
     *
     * @param startTime
     * @param endTime
     * @param shopId
     * @return
     */
    @Query(value = " select * " +
            " from shop_order where " +
            " shop_id = :shopId " +
            " and create_time >= :startTime " +
            " and create_time <= :endTime " +
            " and flag_delete = false " +
            " and time_fix is not null " +
            " and expired_order =true " +
            "and return_cash = false "
            , nativeQuery = true
    )
    List<Order> expiredOrderList(Long startTime, Long endTime, Long shopId);


    /**
     * status是100 带支付类型 营业流水表头总计
     *
     * @param shopId
     * @return
     */
    @Query(value = " select IFNULL(sum(user_cost) , 0) as userCost " +
            " from shop_order where " +
            " shop_id = :shopId " +
            " and create_time >= :startTime " +
            " and create_time <= :endTime " +
            " and flag_delete = false " +
            " and time_fix is not null " +
            " and pay_type_bit & :payTypeBitNumber !=0 " +
            " and status =100 "
            , nativeQuery = true
    )
    Double idOrderHeaderTotal100(Long startTime, Long endTime, Long shopId, long payTypeBitNumber);

    /**
     * 按星期平均
     *
     * @param startTime
     * @param endTime
     * @param shopIds
     * @return
     */
    @Query(value = "SELECT m.w , SUM(m.s)/count(*) from (" +
            " SELECT sum(user_cost) s , DATE_FORMAT(create_date,'%W') w" +
            " from  shop_order WHERE " +
            " shop_id in (:shopIds) AND parent_order = true  " +
            " and create_time>= :startTime and create_time<= :endTime " +
            " and user_cost is not NULL and user_cost != 0" +
            " and time_fix > 0" +
            " and status =100 " +
            " and return_cash = 0 GROUP BY DATE_FORMAT(create_date,'%e')" +
            ") m GROUP BY m.w"
            , nativeQuery = true
    )
    List<Tuple> weekEveryDayAvg(Long startTime, Long endTime, List<Long> shopIds);

    /**
     * 按照小时平均
     *
     * @param startTime
     * @param endTime
     * @param shopIds
     * @return
     */
    @Query(value = "SELECT DATE_FORMAT(create_date,'%H'),sum(user_cost)/count(*)" +
            " from shop_order " +
            " WHERE shop_id in (:shopIds) AND parent_order = true " +
            " and create_time>=:startTime and create_time<=:endTime and user_cost is not NULL " +
            " and user_cost != 0 and return_cash = 0 and status=100 and time_fix > 0" +
            " GROUP BY DATE_FORMAT(create_date,'%H') "
            , nativeQuery = true
    )
    List<Tuple> hourOneDayAvg(Long startTime, Long endTime, List<Long> shopIds);

    /**
     * 当日销售数量
     *
     * @param shopIdList
     * @param startTime
     * @param endTime
     * @param parentOrder
     * @return
     */

    @Query("select shopId as shopId , count(*) as orderNumber from shop_order m where m.shopId in (:shopIdList) " +
            "and m.payTime >= :startTime " +
            "and m.payTime <= :endTime " +
            "and m.parentOrder=:parentOrder " +
            "and m.flagDelete!=true " +
            "and m.status in (8,9,10,100)" +
            "group by m.shopId")
    List<Tuple> sumCountList(List<Long> shopIdList, long startTime, long endTime, boolean parentOrder);
}

